﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data;
using System.Linq;
using DBUtil.Generators;
using DBUtil.Expressions;
using System.Threading;
using DBUtil.Util;
using System.ComponentModel;
using System.Threading.Tasks;
using DBUtil.UsingBlocks;
using DotNetCommon;

namespace DBUtil
{
    /// <summary>
    /// 通用数据库访问对象
    /// </summary>
    public abstract class DBAccess
    {
        #region 设置 Settings
        /// <summary>
        /// 设置
        /// </summary>
        public DBSettings Settings { get; protected set; }
        #endregion

        #region 创建IDbCommand
        /// <summary>
        /// 创建IDbCommand
        /// </summary>
        /// <returns></returns>
        protected abstract IDbCommand CreateCommand();
        #endregion

        #region 创建IDataAdapter
        /// <summary>
        /// 创建IDataAdapter
        /// </summary>
        /// <returns></returns>
        protected abstract IDataAdapter CreateAdapter(IDbCommand cmd);
        #endregion

        #region 长连接状态管理 OpenLongConnectOnce/CloseLongConnectOnce
        /// <summary>
        /// 是否保持连接不断开
        /// </summary>
        private bool IsKeepConnect => _keepConnectCounter > 0;

        /// <summary>
        /// 保持打开数据库连接的次数
        /// </summary>
        private int _keepConnectCounter = 0;

        /// <summary>
        /// 尝试打开一个长连接
        /// </summary>
        public LongConnectBlock OpenLongConnectOnce()
        {
            _keepConnectCounter++;
            if (IsClose) Conn.Open();
            return new LongConnectBlock(this);
        }

        private ReaderLongConnectBlock _openReaderLongConnectOnce()
        {
            _keepConnectCounter++;
            if (IsClose) Conn.Open();
            return new ReaderLongConnectBlock(this);
        }

        /// <summary>
        /// 尝试停止一个长连接
        /// </summary>
        public void CloseLongConnectOnce()
        {
            _keepConnectCounter--;
            if (_keepConnectCounter < 0) _keepConnectCounter = 0;
            if (!IsTran && !IsKeepConnect)
            {
                Conn.Close();
            }
        }
        #endregion

        #region 基础属性        
        /// <summary>
        /// 事物对象
        /// </summary>
        public IDbTransaction Tran { get; protected set; }
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string DBConn { get; protected set; }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public string DBType { get; protected set; }

        /// <summary>
        /// 数据库版本
        /// </summary>
        public virtual string DBVersion { get; }

        /// <summary>
        /// 当前连接的数据库名称
        /// </summary>
        public virtual string DataBase { get; }

        /// <summary>
        /// 默认的架构/模式
        /// </summary>
        public virtual string DefaultSchema { get; }

        /// <summary>
        /// 操作当前数据库的用户名(如果是在sqlserver下,这个值表示当前数据库内的用户名而不是登录数据库使用的用户名,例如: 这里显示的是dbo而不是sa)
        /// </summary>
        public virtual string UserName { get; }

        /// <summary>
        /// 登录数据库使用的用户名
        /// </summary>
        public virtual string LoginUserName { get; set; }

        /// <summary>
        /// 连接对象
        /// </summary>
        public IDbConnection Conn { get; protected set; }
        /// <summary>
        /// 记录是否打开了连接,防止多次打开连接
        /// </summary>
        public bool IsOpen => Conn?.State == ConnectionState.Open;
        /// <summary>
        /// 记录是否关闭了连接,防止多次打开连接
        /// </summary>
        public bool IsClose => Conn?.State == ConnectionState.Closed;
        /// <summary>
        /// 记录是否开启了事务,防止多次开启事务
        /// </summary>
        public bool IsTran => Tran != null;

        /// <summary>
        /// 当前数据库使用的参数的前缀符号
        /// </summary>
        public string ParaPrefix { get; protected set; }
        #endregion

        #region 测试数据库连接
        /// <summary>
        /// 测试数据库连接
        /// </summary>
        public (bool success, string message) OpenTest()
        {
            try
            {
                using (OpenLongConnectOnce()) { }
                return (true, null);
            }
            catch (Exception ex)
            {
                return (false, ex?.Message);
            }
        }

        #endregion

        #region CreatePara 创建参数
        /// <summary>
        /// 创建参数
        /// </summary>
        public abstract IDataParameter CreatePara();

        /// <summary>
        /// 创建具有名称和值的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        public virtual IDataParameter CreatePara(string name, object value)
        {
            var para = CreatePara();
            para.ParameterName = name;
            para.Value = value;
            return para;
        }
        #endregion

        #region 对象映射工具
        /// <summary>
        /// 处理映射:从对象的属性中提取列信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public virtual Dictionary<string, object> MapDictionaryFromObject(object obj) => MapperUtil.MapDictionaryFromObject(obj);

        /// <summary>
        /// 处理映射: 将对象批量插入到表
        /// </summary>
        /// <param name="batchs"></param>
        /// <returns></returns>
        public virtual (string tableName, Dictionary<string, object>)[] MapDictionaryFromObjectBatch_AssignTable((string tableName, object obj)[] batchs) => MapperUtil.MapDictionaryFromObjectBatch_AssignTable(batchs);

        /// <summary>
        /// 处理映射: 将对象批量插入到表
        /// </summary>
        /// <param name="batchs"></param>
        /// <returns></returns>
        public virtual Dictionary<string, object>[] MapDictionaryFromObjectBatch(params object[] batchs) => MapperUtil.MapDictionaryFromObjectBatch(batchs);

        /// <summary>
        /// 处理映射:从对象的属性中提取参数名和参数值信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public virtual IDataParameter[] MapParametersFromObject(object obj) => MapperUtil.MapParametersFromObject(this, obj);
        #endregion

        #region 执行sql语句 ExecuteSql...
        /// <summary>
        /// 执行单个sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回影响的行数</returns>
        public virtual int ExecuteSql(string strSql) => ExecuteSqlBatch(strSql);

        /// <summary>
        /// 执行多个sql语句
        /// </summary>
        /// <param name="sqlArray">多个SQL语句的数组</param>
        /// <returns>返回总共影响的行数</returns>
        public virtual int ExecuteSqlBatch(params string[] sqlArray)
        {
            List<(string, IDataParameter[])> list = new List<(string, IDataParameter[])>();
            foreach (var sql in sqlArray)
            {
                list.Add((sql, null));
            }
            return ExecuteSqlBatch(list.ToArray());
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int ExecuteSql(string strSql, params IDataParameter[] paras) => ExecuteSqlBatch((strSql, paras));

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="para">参数数组,匿名对象,如: new { name = "小明" }</param>
        /// <returns>受影响的行数</returns>
        public virtual int ExecuteSql(string strSql, object para) => ExecuteSql(strSql, MapParametersFromObject(para));

        /// <summary>
        /// 批量执行带参数的sql语句
        /// </summary>
        /// <param name="sqls">带参数的批量sql语句</param>
        /// <returns>总共影响的行数</returns>
        public virtual int ExecuteSqlBatch(params (string sql, IDataParameter[] paramArr)[] sqls)
        {
            int counter = 0;
            var cmd = CreateCommand();
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            using (OpenLongConnectOnce())
            {
                foreach (var (sql, paras) in sqls)
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    if (paras != null)
                    {
                        foreach (var para in paras) cmd.Parameters.Add(para);
                    }
                    counter += cmd.ExecuteNonQuery();
                }
                return counter;
            }
        }

        /// <summary>
        /// 批量执行带参数的sql语句
        /// </summary>
        /// <param name="sqls">带参数的批量sql语句</param>
        /// <returns>总共影响的行数</returns>
        public virtual int ExecuteSqlBatch(params (string sql, object para)[] sqls)
        {
            var newBatch = new List<(string sql, IDataParameter[] paramArr)>();
            foreach (var item in sqls)
            {
                newBatch.Add((item.sql, MapParametersFromObject(item.para)));
            }
            return ExecuteSqlBatch(newBatch.ToArray());
        }
        #endregion

        #region 插入数据 Insert...
        /// <summary>
        /// 向表中插入一行数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项不需要参数化处理,请参照: <code>dic.Add(("now()",false))</code></param>
        /// <returns>返回是受影响的行数</returns>
        public virtual int Insert(string tableName, Dictionary<string, object> dic)
        {
            return InsertBatch((tableName, dic));
        }

        /// <summary>
        /// 向表中插入一行数据，使用匿名对象
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">匿名对象,如: new { name = "小明" }</param>
        public virtual int Insert(string tableName, object obj) => Insert(tableName, MapDictionaryFromObject(obj));

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Insert(string, Dictionary{string, object})"/></param>
        /// <returns>返回是受影响的行数</returns>
        public virtual int InsertBatch(params (string tableName, Dictionary<string, object> dic)[] batchs)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, item.tableName, item.dic, prefix);
                sql += res.sql + "\r\n";
                paras.AddRange(res.paras);
            }
            return ExecuteSql(sql, paras.ToArray());
        }

        /// <summary>
        /// 批量插入数据，使用匿名对象
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Insert(string, Dictionary{string, object})"/></param>
        /// <returns>返回是受影响的行数</returns>
        public virtual int InsertBatch(params (string tableName, object obj)[] batchs) => InsertBatch(MapDictionaryFromObjectBatch_AssignTable(batchs));

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">插入的批量数据</param>
        /// <returns>返回是受影响的行数</returns>
        public virtual int InsertBatch(string tableName, params Dictionary<string, object>[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> dic)>();
            foreach (var item in batchs)
            {
                newBatch.Add((tableName, item));
            }
            return InsertBatch(newBatch.ToArray());
        }

        /// <summary>
        /// 批量插入数据，使用匿名对象
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">插入的批量数据</param>
        /// <returns>返回是受影响的行数</returns>
        public virtual int InsertBatch(string tableName, params object[] batchs) => InsertBatch(tableName, MapDictionaryFromObjectBatch(batchs));

        /// <summary>
        /// 插入一行数据并且返回自增的Id
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项不需要参数化处理,请参照: <code>dic.Add(("now()",false))</code></param>
        /// <returns>返回刚插入的自增Id</returns>
        public virtual long InsertAndGetId(string tableName, Dictionary<string, object> dic)
        {
            return InsertBatchAndGetIds((tableName, dic)).FirstOrDefault();
        }

        /// <summary>
        /// 插入一行数据并且返回自增的Id，使用匿名对象
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">匿名对象,如: new { name = "小明" }</param>
        /// <returns>返回刚插入的自增Id</returns>
        public virtual long InsertAndGetId(string tableName, object obj)
        {
            return InsertBatchAndGetIds((tableName, MapDictionaryFromObject(obj))).FirstOrDefault();
        }

        /// <summary>
        /// 批量插入数据并返回自增的Id
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">参照:<seealso cref="DBAccess.InsertAndGetId(string, Dictionary{string, object})"/></param>
        /// <returns></returns>
        public virtual List<long> InsertBatchAndGetIds(string tableName, params Dictionary<string, object>[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> dic)>();
            foreach (var item in batchs)
            {
                newBatch.Add((tableName, item));
            }
            return InsertBatchAndGetIds(newBatch.ToArray());
        }

        /// <summary>
        /// 批量插入数据并返回自增的Id，使用匿名对象
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">参照:<seealso cref="DBAccess.InsertAndGetId(string, Dictionary{string, object})"/></param>
        /// <returns></returns>
        public virtual List<long> InsertBatchAndGetIds(string tableName, params object[] batchs) => InsertBatchAndGetIds(tableName, MapDictionaryFromObjectBatch(batchs));

        /// <summary>
        /// 批量插入数据并返回自增的Id
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.InsertAndGetId(string, Dictionary{string, object})"/></param>
        /// <returns></returns>
        public virtual List<long> InsertBatchAndGetIds(params (string tableName, Dictionary<string, object> dic)[] batchs)
        {
            var resids = new List<long>();
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, item.tableName, item.dic, prefix);
                sql += res.sql + " Select SCOPE_IDENTITY();" + "\r\n";
                paras.AddRange(res.paras);
            }
            var ds = SelectDataSet(sql, paras.ToArray());
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                var tbl = ds.Tables[i];
                var id = tbl.Rows[0][0].ToString();
                if (long.TryParse(id, out long id2))
                {
                    resids.Add(id2);
                }
            }
            return resids;
        }

        /// <summary>
        /// 批量插入数据并返回自增的Id
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.InsertAndGetId(string, Dictionary{string, object})"/></param>
        /// <returns></returns>
        public virtual List<long> InsertBatchAndGetIds(params (string tableName, object obj)[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> dic)>();
            foreach (var item in batchs)
            {
                newBatch.Add((item.tableName, MapDictionaryFromObject(item.obj)));
            }
            return InsertBatchAndGetIds(newBatch.ToArray());
        }
        #endregion

        #region 更新数据 Update...
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">键值表</param>
        /// <param name="filterSql">过滤条件sql以and开头</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> dic, string filterSql)
        {
            return Update(tableName, dic, filterSql, null);
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">键值表</param>
        /// <param name="filterParas">过滤条件</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> dic, Dictionary<string, object> filterParas)
        {
            return UpdateBatch((tableName, dic, filterParas));
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setParas">键值表</param>
        /// <param name="filterParas">过滤条件</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, object setParas, object filterParas) => Update(tableName, MapDictionaryFromObject(setParas), MapDictionaryFromObject(filterParas));

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">键值表</param>
        /// <param name="filterParas">过滤条件</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> dic, object filterParas) => Update(tableName, dic, MapDictionaryFromObject(filterParas));

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">键值表</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <param name="filterParas">过滤条件中的参数数组</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> dic, string filterSql, params IDataParameter[] filterParas)
        {
            return UpdateBatch((tableName, dic, filterSql, filterParas));
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setParas">键值表</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <param name="filterParas">过滤条件中的参数数组</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, object setParas, string filterSql, params IDataParameter[] filterParas)
        {
            return UpdateBatch((tableName, MapDictionaryFromObject(setParas), filterSql, filterParas));
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setParas">键值表</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <param name="filterParas">过滤条件中的参数数组</param>
        /// <returns>影响的行数</returns>
        public virtual int Update(string tableName, object setParas, string filterSql, object filterParas)
        {
            return UpdateBatch((tableName, MapDictionaryFromObject(setParas), filterSql, MapParametersFromObject(filterParas)));
        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Update(string, Dictionary{string, object}, string)"/></param>
        /// <returns>影响的行数</returns>
        public virtual int UpdateBatch(params (string tableName, Dictionary<string, object> dic, string filterSql)[] batchs)
        {
            var batchs2 = new List<(string tableName, Dictionary<string, object> dic, string filterSql, IDataParameter[] filterParas)>();
            batchs.ToList().ForEach(t => batchs2.Add((t.tableName, t.dic, t.filterSql, null)));
            return UpdateBatch(batchs2.ToArray());
        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Update(string, Dictionary{string, object}, Dictionary{string, object})"/></param>
        /// <returns>影响的行数</returns>
        public virtual int UpdateBatch(params (string tableName, Dictionary<string, object> dic, Dictionary<string, object> paras)[] batchs)
        {
            var batchs2 = new List<(string tableName, Dictionary<string, object> dic, string filterSql, IDataParameter[] filterParas)>();
            var index = 0;
            batchs.ToList().ForEach(t =>
            {
                var (filterSql, paras) = SqlUtil.GenerateFilterSql(this, t.paras, $"p_{index++}", null);
                batchs2.Add((t.tableName, t.dic, filterSql, paras));
            });
            return UpdateBatch(batchs2.ToArray());
        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Update(string, Dictionary{string, object}, string, IDataParameter[])"/></param>
        /// <returns>影响的行数</returns>
        public virtual int UpdateBatch(params (string tableName, Dictionary<string, object> dic, string filterSql, IDataParameter[] filterParas)[] batchs)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var arg = batchs[i];
                var (sql2, paras2) = SqlUtil.GenerateUpdateSql(this, arg.tableName, arg.dic, $"__p_{i}", arg.filterSql, arg.filterParas);
                sql += "\r\n" + sql2;
                if (paras2 != null) paras.AddRange(paras2);
            }
            return ExecuteSql(sql, paras.ToArray());
        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Update(string, Dictionary{string, object}, Dictionary{string, object})"/></param>
        /// <returns>影响的行数</returns>
        public virtual int UpdateBatch(params (string tableName, object setParas, object paras)[] batchs)
        {
            var batchs2 = new List<(string tableName, Dictionary<string, object> dic, Dictionary<string, object> filterDic)>();
            batchs.ToList().ForEach(t =>
            {
                batchs2.Add((t.tableName, MapDictionaryFromObject(t.setParas), MapDictionaryFromObject(t.paras)));
            });
            return UpdateBatch(batchs2.ToArray());
        }
        #endregion

        #region 插入或更新数据 UpdateOrInsert...
        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">插入用的键值表</param>
        /// <param name="updateDic">更新用的键值表</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, string filterSql) => UpdateOrInsert(tableName, insertDic, updateDic, filterSql, null);

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">插入用的键值表</param>
        /// <param name="updateDic">更新用的键值表</param>
        /// <param name="filterDic">过滤用的键值表</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, Dictionary<string, object> filterDic)
        {
            var (filterSql, paras2) = SqlUtil.GenerateFilterSql(this, filterDic, $"p_", null);
            return UpdateOrInsert(tableName, insertDic, updateDic, filterSql, paras2);
        }

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">插入用的键值表</param>
        /// <param name="updateDic">更新用的键值表</param>
        /// <param name="paras">过滤参数,使用匿名对象,如: new { id =1 }</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, object paras) =>
            UpdateOrInsert(tableName, insertDic, updateDic, MapDictionaryFromObject(paras));

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertObj">插入用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="updateObj">更新用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="paras">用于过滤的匿名对象,如: new { id = 1 }</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, object insertObj, object updateObj, object paras) => UpdateOrInsert(tableName, MapDictionaryFromObject(insertObj), MapDictionaryFromObject(updateObj), paras);

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertObj">插入用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="updateObj">更新用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="filterSql">过滤条件</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, object insertObj, object updateObj, string filterSql) => UpdateOrInsert(tableName, MapDictionaryFromObject(insertObj), MapDictionaryFromObject(updateObj), filterSql);

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertObj">插入用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="updateObj">更新用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="filterSql">过滤条件</param>
        /// <param name="paras">过滤条件中的参数数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, object insertObj, object updateObj, string filterSql, params IDataParameter[] paras) => UpdateOrInsert(tableName, MapDictionaryFromObject(insertObj), MapDictionaryFromObject(updateObj), filterSql, paras);

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertObj">插入用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="updateObj">更新用的匿名对象,如: new { name = "小明" }</param>
        /// <param name="filterSql">过滤条件</param>
        /// <param name="paras">过滤条件中的参数数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, object insertObj, object updateObj, string filterSql, object paras) => UpdateOrInsert(tableName, MapDictionaryFromObject(insertObj), MapDictionaryFromObject(updateObj), filterSql, MapParametersFromObject(paras));

        /// <summary>
        /// 插入或更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">插入用的键值表</param>
        /// <param name="updateDic">更新用的键值表</param>
        /// <param name="filterSql">判断是更新还是插入的条件</param>
        /// <param name="paras">用于过滤的字典</param>
        /// <returns></returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, string filterSql, params IDataParameter[] paras)
        {
            using (OpenLongConnectOnce())
            {
                if (SelectScalar<string>($"select count(1) from {tableName} where 1=1 {filterSql}", paras) == "0")
                {
                    return Insert(tableName, insertDic);
                }
                else
                {
                    return Update(tableName, updateDic, filterSql, paras);
                }
            };
        }
        #endregion

        #region 删除数据 Delete...
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int Delete(string tableName, string filterSql) => Delete(tableName, filterSql, null);

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <param name="filterParas">过滤条件中的参数集合</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int Delete(string tableName, string filterSql, params IDataParameter[] filterParas) => DeleteBatch((tableName, filterSql, filterParas));

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filterSql">过滤条件以and开头</param>
        /// <param name="paras">过滤条件中的参数,使用匿名对象,如: new { name="小明" }</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int Delete(string tableName, string filterSql, object paras) => Delete(tableName, filterSql, MapParametersFromObject(paras));

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filterDic">用于生成过滤条件的字典</param>
        /// <returns></returns>
        public virtual int Delete(string tableName, Dictionary<string, object> filterDic) => DeleteBatch((tableName, filterDic));

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filterObj">用于生成过滤条件的参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual int Delete(string tableName, object filterObj) => Delete(tableName, MapDictionaryFromObject(filterObj));

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="batchs">批量删除条件</param>
        /// <returns></returns>
        public virtual int DeleteBatch(params (string tableName, Dictionary<string, object> filterDic)[] batchs)
        {
            var newBatch = new List<(string tableName, string filterSql, IDataParameter[] filterParas)>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                var res = SqlUtil.GenerateFilterSql(this, item.filterDic, $"p_{i}", null);
                newBatch.Add((item.tableName, res.filterSql, res.filterParas));
            }
            return DeleteBatch(newBatch.ToArray());
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="batchs">批量删除条件</param>
        /// <returns></returns>
        public virtual int DeleteBatch(params (string tableName, object filterObj)[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> filterParas)>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                newBatch.Add((item.tableName, MapDictionaryFromObject(item.filterObj)));
            }
            return DeleteBatch(newBatch.ToArray());
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">批量删除条件</param>
        /// <returns></returns>
        public virtual int DeleteBatch(string tableName, params Dictionary<string, object>[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> filterDic)>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                newBatch.Add((tableName, item));
            }
            return DeleteBatch(newBatch.ToArray());
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="batchs">批量删除条件</param>
        /// <returns></returns>
        public virtual int DeleteBatch(string tableName, params object[] batchs)
        {
            var newBatch = new List<(string tableName, Dictionary<string, object> filterDic)>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                newBatch.Add((tableName, MapDictionaryFromObject(item)));
            }
            return DeleteBatch(newBatch.ToArray());
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Delete(string, string)"/></param>
        /// <returns>返回受影响的行数</returns>
        public virtual int DeleteBatch(params (string tableName, string filterSql)[] batchs)
        {
            var batchs2 = new List<(string tableName, string filterSql, IDataParameter[] filterParas)>();
            batchs.ToList().ForEach(t => batchs2.Add((t.tableName, t.filterSql, null)));
            return DeleteBatch(batchs2.ToArray());
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="batchs">参照:<seealso cref="DBAccess.Delete(string, string, IDataParameter[])"/></param>
        /// <returns>返回受影响的行数</returns>
        public virtual int DeleteBatch(params (string tableName, string filterSql, IDataParameter[] filterParas)[] batchs)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < batchs.Length; i++)
            {
                var item = batchs[i];
                var resSql = SqlUtil.GenerateDeleteSql(this, item.tableName, item.filterSql);
                sql += resSql + "\r\n";
                if (item.filterParas != null) paras.AddRange(item.filterParas);
            }
            return ExecuteSql(sql, paras.ToArray());
        }
        #endregion

        #region 查询语句 Select...
        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql) => SelectScalar<T>(strSql, null);

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="obj">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql, object obj) => SelectScalar<T>(strSql, MapParametersFromObject(obj));

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql, params IDataParameter[] paraArr)
        {
            object res = null;
            if (IsInCache)
            {
                res = GetFromCache(new CacheKey()
                {
                    Sql = strSql,
                    Parameters = paraArr
                });
                if (res != null) return (T)res;
            }
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            using (OpenLongConnectOnce()) { res = cmd.ExecuteScalar(); }
            cmd.Parameters.Clear();
            if (IsInCache)
            {
                SetCacheItem(new CacheKey()
                {
                    Sql = strSql,
                    Parameters = paraArr
                }, res);
            }
            if (res is DBNull)
            {
                if (default(T) == null) return default(T);
                else throw new Exception($"查询结果为Null,无法转化为{typeof(T)}");
            }
            var type = typeof(T);
            if (type.FullName.StartsWith("System.Nullable`1"))
            {
                //NullAble类型
                if (res == null) return default(T);
                //可空枚举
                else if (type.GenericTypeArguments[0].IsEnum) return (T)Enum.Parse(type.GenericTypeArguments[0], res?.ToString());
                //可空bool
                else if (type.GenericTypeArguments[0].FullName.StartsWith("System.Boolean")) return (T)((object)Convert.ToBoolean(res));
                else return (T)res;
            }
            else
            {
                if (type.FullName.StartsWith("System.Boolean"))
                {
                    return (T)((object)Convert.ToBoolean(res));
                }
                var converter = TypeDescriptor.GetConverter(typeof(T));
                try
                {
                    return (T)res;
                }
                catch
                {
                    return (T)converter.ConvertTo(res, type);
                }

            }
        }

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql) => SelectDataSet(strSql, null);

        /// <summary>
        /// 返回查询结果的数据集,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql, object paras) => SelectDataSet(strSql, MapParametersFromObject(paras));

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql, params IDataParameter[] paraArr)
        {
            if (IsInCache)
            {
                var res = GetFromCache(new CacheKey()
                {
                    Sql = strSql,
                    Parameters = paraArr
                });
                var res2 = res as DataSet;
                if (res2 != null) return res2;
            }
            DataSet set = new DataSet();
            var cmd = CreateCommand();
            cmd.Connection = Conn;
            cmd.CommandText = strSql;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            if (IsTran) cmd.Transaction = Tran;
            var adp = CreateAdapter(cmd);
            using (OpenLongConnectOnce()) { adp.Fill(set); };
            cmd.Parameters.Clear();
            if (IsInCache)
            {
                SetCacheItem(new CacheKey()
                {
                    Sql = strSql,
                    Parameters = paraArr
                }, set);
            }
            return set;
        }

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql) => SelectDataTable(strSql, null);

        /// <summary>
        /// 返回查询结果的数据表,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql, object paras) => SelectDataTable(strSql, MapParametersFromObject(paras));

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql, params IDataParameter[] paraArr)
        {
            var ds = SelectDataSet(strSql, paraArr);
            if (ds.Tables.Count > 0) return ds.Tables[0];
            else return null;
        }

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql) => SelectDataReader(strSql, null);

        /// <summary>
        /// 获取阅读器,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql, object paras) => SelectDataReader(strSql, MapParametersFromObject(paras));

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql, params IDataParameter[] paraArr)
        {
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            if (IsTran) cmd.Transaction = Tran;
            var block = _openReaderLongConnectOnce();
            IDataReader reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            block.Reader = reader;
            return block;
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql) where T : class, new() => SelectModels<T>(sql, null).FirstOrDefault();

        /// <summary>
        /// 查询到单个模型,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql, object paras) where T : class, new() => SelectModel<T>(sql, MapParametersFromObject(paras));

        /// <summary>
        /// 查询到单个模型,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual T SelectModelFast<T>(string tableName, object paras = null) where T : class, new() => SelectModelsFast<T>(tableName, paras).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual List<T> SelectModelsFast<T>(string tableName, object paras = null) where T : class, new()
        {
            var (filter, paras2) = SqlUtil.GenerateFilterSql(this, MapDictionaryFromObject(paras), "p_", null);
            var sql = $"select * from {tableName} where 1=1 {filter}";
            return SelectModels<T>(sql, paras2);
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql, params IDataParameter[] filterParameters) where T : class, new() => SelectModels<T>(sql, filterParameters).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql) where T : class, new() => SelectModels<T>(sql, null);

        /// <summary>
        /// 查询到模型集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql, object paras) where T : class, new() => SelectModels<T>(sql, MapParametersFromObject(paras));

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql, params IDataParameter[] filterParameters) where T : class, new()
        {
            var dics = SelectDictionarys(sql, filterParameters);
            var res = new List<T>();
            foreach (var item in dics)
            {
                res.Add(MapperUtil.MappingSelect<T>(item));
            }
            return res;
        }

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql) => SelectDictionarys(sql, null).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql, object paras) => SelectDictionarys(sql, MapParametersFromObject(paras)).FirstOrDefault();

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql, params IDataParameter[] filterParameters) => SelectDictionarys(sql, filterParameters).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql) => SelectDictionarys(sql, null);

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">sql语句参数,使用匿名对象,如: new { name = "小明" }</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql, object paras) => SelectDictionarys(sql, MapParametersFromObject(paras));

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql, params IDataParameter[] filterParameters)
        {
            var res = new List<Dictionary<string, object>>();
            var dt = SelectDataTable(sql, filterParameters);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var row = dt.Rows[i];
                var dic = new Dictionary<string, object>();
                for (int j = 0, len2 = dt.Columns.Count; j < len2; j++)
                {
                    dic.Add(dt.Columns[j].ColumnName, row[j]);
                }
                res.Add(dic);
            }
            return res;
        }
        #endregion

        #region 事务操作
        private int KeepTransactionCount = 0;
        /// <summary>
        /// 开启一次事务(不支持嵌套事务,如果当前已经在事务中并且隔离级别大于新指定的隔离级别,那么就忽略本次的开启事务操作)
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别(默认为:ReadCommitted)</param>
        private void BeginTransactionOnce(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (IsTran && Tran.IsolationLevel < isolationLevel) throw new Exception($"已经在事务中,并且当前的事务隔离级别({Tran.IsolationLevel.ToString()})小于新指定的隔离级别({isolationLevel.ToString()})!");
            KeepTransactionCount++;
            if (!IsTran)
            {
                OpenLongConnectOnce();
                Tran = Conn.BeginTransaction(isolationLevel);
            }
        }

        /// <summary>
        /// 提交一次事务
        /// </summary>
        private void CommitTransactionOnce()
        {
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                Tran.Commit();
                Tran = null;
                CloseLongConnectOnce();
            }
        }

        /// <summary>
        /// 回滚一次事务
        /// </summary>
        private void RollbackTransactionOnce()
        {
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                Tran.Rollback();
                Tran = null;
                CloseLongConnectOnce();
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="action">要运行的代码</param>
        public void RunInTransaction(Action action, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                BeginTransactionOnce(isolationLevel);
                action();
                CommitTransactionOnce();
            }
            catch
            {
                RollbackTransactionOnce();
                throw;
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="func">要运行的代码</param>
        public T RunInTransaction<T>(Func<T> func, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                BeginTransactionOnce(isolationLevel);
                var res = func();
                CommitTransactionOnce();
                return res;
            }
            catch
            {
                RollbackTransactionOnce();
                throw;
            }
        }
        #endregion

        #region 生成分页查询语句: GetSqlForPageSize
        /// <summary>
        /// 生成分页查询语句
        /// </summary>
        /// <param name="selectSql">select子句,如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序子句,必须指定,如:order by id</param>
        /// <param name="PageSize">分页大小,如:10</param>
        /// <param name="PageIndex">当前页码,如:1</param>
        /// <returns>返回示例(mysql):select id,name from person where age>10 order by id limit 0,10</returns>
        public abstract string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex);
        #endregion

        #region Judge系列: 判断表/视图/列/存储过程/触发器是否存在
        /// <summary>
        /// 判断指定表或视图中是否有某一列
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInTableOrView(string tableOrViewName, string columnName)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{tableOrViewName}' and COLUMN_NAME='{columnName}'";
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断指定表中是否有某一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInTable(string tableName, string columnName)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{columnName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='BASE TABLE'";
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断指定视图中是否有某一列
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInView(string viewName, string columnName)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{viewName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='VIEW'";
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断表或视图是否存在
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <returns>返回表或视图是否存在</returns>
        public virtual bool JudgeTableOrViewExist(string tableOrViewName)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}'", tableOrViewName);
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>返回表是否存在</returns>
        public virtual bool JudgeTableExist(string tableName)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='BASE TABLE'", tableName);
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断视图是否存在
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <returns>返回视图是否存在</returns>
        public virtual bool JudgeViewExist(string viewName)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='VIEW'", viewName);
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public abstract bool JudgeProcedureExist(string procName);

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <returns></returns>
        public abstract bool JudgeTriggerExist(string triggerName);
        #endregion

        #region CreateNewDB
        /// <summary>
        /// 根据当前的数据库类型和连接字符串创建一个新的数据库操作对象
        /// </summary>
        /// <returns></returns>
        public DBAccess CreateNewDB(Action<DBSettings> reset = null)
        {
            var settings = Settings.Clone() as DBSettings;
            reset?.Invoke(settings);
            return DBFactory.CreateDB(DBType, DBConn, settings);
        }
        #endregion

        #region 缓存功能
        /// <summary>
        /// 是否在缓存中
        /// </summary>
        public bool IsInCache { get; protected set; }
        /// <summary>
        /// 缓存字典
        /// </summary>
        private Dictionary<CacheKey, object> CacheDir = new Dictionary<CacheKey, object>();
        /// <summary>
        /// 开启缓存
        /// </summary>
        /// <param name="clearBefore">如果之前存在缓存,是否清除</param>
        public virtual void BeginCache(bool clearBefore = true)
        {
            if (IsInCache && clearBefore)
            {
                CacheDir.Clear();
            }
            IsInCache = true;
        }

        /// <summary>
        /// 清除缓存
        /// </summary>
        public virtual void ClearCache()
        {
            CacheDir.Clear();
        }

        /// <summary>
        /// 如果当前已开启存在,就关闭
        /// </summary>
        public virtual void EndCache()
        {
            CacheDir.Clear();
            IsInCache = false;
        }

        /// <summary>
        /// 从缓存中获取结果
        /// </summary>
        /// <param name="key">缓存键</param>
        /// <returns></returns>
        protected virtual object GetFromCache(CacheKey key)
        {
            if (key == null) return null;
            foreach (var item in CacheDir)
            {
                if (CompareCacheKey(item.Key, key)) return item.Value;
            }
            return null;
        }

        /// <summary>
        /// 比较两个缓存键是否相同
        /// </summary>
        protected virtual bool CompareCacheKey(CacheKey key1, CacheKey key2)
        {
            if (key1 == null || key2 == null) return false;
            if (key1.Sql == key2.Sql)
            {
                var hasParaCache = key1.Parameters != null && key1.Parameters.Length > 0;
                var hasPara = key2.Parameters != null && key2.Parameters.Length > 0;
                var count = 0;
                count += hasParaCache ? 1 : 0;
                count += hasPara ? 1 : 0;
                //都没有参数缓存的时候
                if (count == 0) return true;
                //有一个有参数缓存
                if (count == 1) return false;
                //都有参数缓存的时候
                //参数格式不相等的时候
                var flag = true;
                for (int i = 0, len = key2.Parameters.Length; i < len; i++)
                {
                    var para1 = key2.Parameters[i];
                    var para2 = key1.Parameters[i];
                    if (para1.ParameterName != para2.ParameterName || para1.DbType != para2.DbType)
                    {
                        flag = false;
                        break;
                    }
                    if (para1.DbType == DbType.String || para1.DbType == DbType.Int32
                        || para1.DbType == DbType.Date
                        || para1.DbType == DbType.DateTime
                        || para1.DbType == DbType.Double)
                    { }
                    else
                    {
                        flag = false;
                        break;
                    }
                    if (para1.Value.ToString() != para2.Value.ToString())
                    {
                        flag = false;
                        break;
                    }
                }
                return flag;
            }
            return false;
        }

        /// <summary>
        /// 设置缓存项
        /// </summary>
        /// <param name="key">缓存键</param>
        /// <param name="value">缓存值</param>
        protected virtual void SetCacheItem(CacheKey key, object value)
        {
            if (key == null || value == null) return;
            CacheKey itemKey = null;
            foreach (var item in CacheDir)
            {
                if (CompareCacheKey(key, item.Key))
                {
                    itemKey = item.Key;
                    break;
                }
            }
            if (itemKey != null) CacheDir.Remove(itemKey);
            CacheDir.Add(key, value);
        }

        /// <summary>
        /// 缓存关键字
        /// </summary>
        public class CacheKey
        {
            /// <summary>
            /// 执行的sql语句
            /// </summary>
            public string Sql { set; get; }

            /// <summary>
            /// 参数对象
            /// </summary>
            public IDataParameter[] Parameters { set; get; }
        }
        #endregion

        #region Id和流水号生成控制器
        private IdAndSNOGeneratorProcessCache _processCacheGenerator = null;
        /// <summary>
        /// Id和流水号生成器,单机环境使用(基于应用程序缓存)
        /// </summary>
        public IdAndSNOGeneratorProcessCache GeneratorProcessCache
        {
            get
            {
                if (_processCacheGenerator == null) _processCacheGenerator = new IdAndSNOGeneratorProcessCache(this);
                return _processCacheGenerator;
            }
        }

        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        public abstract long NewId(string tableName, string colName);

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        public abstract List<long> NewIds(string tableName, string colName, int count);

        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <returns></returns>
        public abstract void ResetId(string tableName, string colName);

        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        public abstract string NewSNO(string tableName, string colName, SerialFormat format);

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        public abstract List<string> NewSNOs(string tableName, string colName, SerialFormat format, int count);

        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        public abstract void ResetSNO(string tableName, string colName);
        #endregion

        #region 基于数据库的分布式锁 RunInLock/TryGetLock/TryReleaseLock
        private static ConcurrentDictionary<string, bool> _lockcaches = new ConcurrentDictionary<string, bool>();
        private static ConcurrentDictionary<string, CancellationTokenSource> _locktokens = new ConcurrentDictionary<string, CancellationTokenSource>();

        private void EnsureInitLock()
        {
            if (!_lockcaches.ContainsKey(DBConn))
            {
                lock (typeof(DBAccess))
                {
                    if (!_lockcaches.ContainsKey(DBConn))
                    {
                        if (!JudgeTableExist(Settings.DBLockTableName))
                        {
                            #region 新建表 Settings.DBLockTableName
                            ExecuteSql($@"
create table {Settings.DBLockTableName}(
	lock_str varchar(200) unique not null,
	lock_time {Expression_DateTime.DefaultDateTimeType} default({Expression_DateTime.Current}),
    lock_createtime {Expression_DateTime.DefaultDateTimeType} default({Expression_DateTime.Current}),
	lock_user varchar(200) not null
)");
                            #endregion
                        }
                        _lockcaches.TryAdd(DBConn, true);
                    }
                }
            }
        }

        private (bool success, string message) TryGetLock(string lock_str, string lock_user, long timeoutSecond = 60 * 5)
        {
            return ResultUtil.WrapRes(() =>
            {
                var starttime = DateTime.Now;
                var lockkey = GetLockKey(lock_str, lock_user);
                //单机做一次拦截
                while (true)
                {
                    if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
                    {
                        //指定的锁已经在使用中       
                        var endtime = DateTime.Now;
                        var span = endtime - starttime;
                        if (span.TotalSeconds > timeoutSecond)
                        {
                            throw new Exception($"获取锁[{lock_str}]超时!");
                        }
                        else
                        {
                            continue;
                        }
                    }
                    break;
                }
                //数据库层拦截
                using (OpenLongConnectOnce())
                {
                    EnsureInitLock();
                    while (true)
                    {
                        try
                        {
                            ExecuteSql($"insert into {Settings.DBLockTableName}(lock_str,lock_user) values('{lock_str}','{lock_user}')");
                            StartLockMonitor(lock_str, lock_user);
                            break;
                        }
                        catch (Exception ex)
                        {
                            var endtime = DateTime.Now;
                            var span = endtime - starttime;
                            if (span.TotalSeconds > timeoutSecond)
                            {
                                throw new Exception($"获取锁[{lock_str}]超时:{ex?.Message}]", ex);
                            }
                            Thread.Sleep(1000);
                            //删除30秒之前的
                            ExecuteSql($"delete from {Settings.DBLockTableName} where lock_time <{Expression_DateTime.GetCurrentAddSecond(-30)}");
                        }
                    }
                }
            });
        }

        private void StartLockMonitor(string lock_str, string lock_user)
        {
            var cancellationTokenSource = new CancellationTokenSource();
            _locktokens.TryAdd(GetLockKey(lock_str, lock_user), cancellationTokenSource);
            Task.Run(() =>
            {
                DBAccess db2 = CreateNewDB();
                while (true)
                {
                    if (cancellationTokenSource.IsCancellationRequested) break;
                    //休息10秒
                    Thread.Sleep(1000 * 10);
                    //将当前的锁刷新时长
                    int count = db2.ExecuteSql($"update {Settings.DBLockTableName} set lock_time= {Expression_DateTime.Current} where lock_str='{lock_str}' and lock_user='{lock_user}'");
                    if (count != 1) break;
                }
            });
        }

        /// <summary>
        /// 在分布式锁内执行代码逻辑
        /// </summary>
        /// <param name="lock_str">锁定的资源名称</param>
        /// <param name="lock_user">锁的持有者</param>
        /// <param name="action">要执行的代码逻辑</param>
        /// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        public void RunInLock(string lock_str, string lock_user, Action action, long getLockTimeoutSecond = 60 * 5)
        {
            var res = TryGetLock(lock_str, lock_user, getLockTimeoutSecond);
            if (res.success)
            {
                try
                {
                    action();
                }
                finally
                {
                    TryReleaseLock(lock_str, lock_user);
                }
            }
            else
            {
                throw new Exception(res.message);
            }
        }

        /// <summary>
        /// 在分布式锁内执行代码逻辑,如果获取锁超时则抛出异常
        /// </summary>
        /// <param name="lock_str">锁定的资源名称</param>
        /// <param name="lock_user">锁的持有者</param>
        /// <param name="func">要执行的代码逻辑</param>
        /// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        public T RunInLock<T>(string lock_str, string lock_user, Func<T> func, long getLockTimeoutSecond = 60 * 5)
        {
            var res = TryGetLock(lock_str, lock_user, getLockTimeoutSecond);
            if (res.success)
            {
                try
                {
                    return func();
                }
                finally
                {
                    TryReleaseLock(lock_str, lock_user);
                }
            }
            else
            {
                throw new TimeoutException(res.message);
            }
        }

        private void TryReleaseLock(string lock_str, string lock_user)
        {
            var lockkey = GetLockKey(lock_str, lock_user);
            EnsureInitLock();
            if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
            {
                token.Cancel();
                _locktokens.TryRemove(lockkey, out CancellationTokenSource token2);
            }
            ExecuteSql($"delete from {Settings.DBLockTableName} where lock_str='{lock_str}' and lock_user='{lock_user}'");
        }

        private string GetLockKey(string lock_str, string lock_user)
        {
            return $"{lock_str}_{lock_user}";
        }
        #endregion

        #region 数据库管理对象 Manage
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public abstract DBManage Manage { get; }
        #endregion

        #region 数据库通用增删改查帮助类 DataHelper
        /// <summary>
        /// 数据库通用增删改查帮助类
        /// </summary>
        public virtual DataHelper DataHelper => new DataHelper(this);
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        public abstract DateTimeExpression Expression_DateTime { get; }
        #endregion
    }
}
